using Dapper;
using Lzfy_His_Service.Models;
using Lzfy_His_Service.Services;
using Oracle.ManagedDataAccess.Client;
using System.Diagnostics;
using System.Data;

namespace Lzfy_His_Service.Repositories
{
    public class RefundTaskMasterRepository : IRefundTaskMasterRepository
    {
        private readonly IOracleConnectionFactory _connectionFactory;
        private readonly ILogger<RefundTaskMasterRepository> _logger;
        private readonly ConnectionLeakDetector _leakDetector;
        private readonly OracleConnectionPoolOptimizer _poolOptimizer;

        public RefundTaskMasterRepository(
            IOracleConnectionFactory connectionFactory,
            ILogger<RefundTaskMasterRepository> logger,
            ConnectionLeakDetector leakDetector,
            OracleConnectionPoolOptimizer poolOptimizer)
        {
            _connectionFactory = connectionFactory;
            _logger = logger;
            _leakDetector = leakDetector;
            _poolOptimizer = poolOptimizer;
        }

        public async Task<RefundTaskMaster?> GetByIdAsync(long id)
        {
            var operationId = Guid.NewGuid().ToString("N")[..8];
            var stopwatch = Stopwatch.StartNew();
            
            try
            {
                using var connection = _connectionFactory.CreateConnection();
                if (connection is not Oracle.ManagedDataAccess.Client.OracleConnection oracleConnection)
                {
                    throw new InvalidOperationException($"期望Oracle连接，但得到{connection.GetType().Name}");
                }
                using var trackedConnection = oracleConnection.TrackConnection(_leakDetector, $"RefundTaskMasterRepository.GetByIdAsync({id})", operationId);
                
                const string sql = @"
                    SELECT ID as Id, 
                           INPUT_DATE as InputDate, 
                           USERNAME as Username, 
                           FULLNAME as Fullname, 
                           ACCOUNT_COUNT as AccountCount, 
                           ACCOUNT_SUM_BALANCE as AccountSumBalance
                    FROM PCA.LZFY_REFUND_TASK_MASTER
                    WHERE ID = :id";

                var result = await connection.QueryFirstOrDefaultAsync<RefundTaskMaster>(sql, new { id });
                
                _logger.LogInformation("Oracle查询成功: {Operation} {Duration}ms {ConnectionId}", 
                    $"GetByIdAsync({id})", stopwatch.ElapsedMilliseconds, operationId);
                    
                return result;
            }
            catch (OracleException ex) when (ex.Number == 50012 || ex.Message.Contains("Pooled connection request timed out"))
            {
                _logger.LogError(ex, "Oracle连接池耗尽，获取退费任务主表失败: {Id}", id);
                var poolStats = await _poolOptimizer.GetPoolStatsAsync();
                _logger.LogWarning("当前连接池状态: {PoolStats}", poolStats);
                throw new InvalidOperationException("数据库连接繁忙，请稍后重试", ex);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error getting refund task master by id: {Id} (Operation: {OperationId})", id, operationId);
                throw;
            }
        }

        public async Task<IEnumerable<RefundTaskMaster>> GetAllAsync()
        {
            try
            {
                using var connection = _connectionFactory.CreateConnection();
                const string sql = @"
                    SELECT ID as Id, 
                           INPUT_DATE as InputDate, 
                           USERNAME as Username, 
                           FULLNAME as Fullname, 
                           ACCOUNT_COUNT as AccountCount, 
                           ACCOUNT_SUM_BALANCE as AccountSumBalance
                    FROM PCA.LZFY_REFUND_TASK_MASTER
                    ORDER BY INPUT_DATE DESC";

                return await connection.QueryAsync<RefundTaskMaster>(sql);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error getting all refund task masters");
                throw;
            }
        }

        public async Task<(IEnumerable<RefundTaskMaster> data, int totalCount)> GetWithFilterAsync(
            DateTime? startDate = null,
            DateTime? endDate = null,
            string? username = null,
            string? fullname = null,
            int pageIndex = 1,
            int pageSize = 20)
        {
            try
            {
                using var connection = _connectionFactory.CreateConnection();
                
                var whereConditions = new List<string>();
                var parameters = new DynamicParameters();

                if (startDate.HasValue)
                {
                    whereConditions.Add("INPUT_DATE >= :startDate");
                    parameters.Add("startDate", startDate.Value);
                }

                if (endDate.HasValue)
                {
                    whereConditions.Add("INPUT_DATE <= :endDate");
                    parameters.Add("endDate", endDate.Value);
                }

                if (!string.IsNullOrEmpty(username))
                {
                    whereConditions.Add("UPPER(USERNAME) LIKE UPPER(:username)");
                    parameters.Add("username", $"%{username}%");
                }

                if (!string.IsNullOrEmpty(fullname))
                {
                    whereConditions.Add("UPPER(FULLNAME) LIKE UPPER(:fullname)");
                    parameters.Add("fullname", $"%{fullname}%");
                }

                var whereClause = whereConditions.Count > 0 ? "WHERE " + string.Join(" AND ", whereConditions) : "";

                // Count query
                var countSql = $@"
                    SELECT COUNT(*)
                    FROM PCA.LZFY_REFUND_TASK_MASTER
                    {whereClause}";

                var totalCount = await connection.QuerySingleAsync<int>(countSql, parameters);

                // Data query with pagination
                var offset = (pageIndex - 1) * pageSize;
                parameters.Add("offset", offset);
                parameters.Add("pageSize", pageSize);

                var dataSql = $@"
                    SELECT * FROM (
                        SELECT ID as Id, 
                               INPUT_DATE as InputDate, 
                               USERNAME as Username, 
                               FULLNAME as Fullname, 
                               ACCOUNT_COUNT as AccountCount, 
                               ACCOUNT_SUM_BALANCE as AccountSumBalance,
                               ROW_NUMBER() OVER (ORDER BY INPUT_DATE DESC) AS rn
                        FROM PCA.LZFY_REFUND_TASK_MASTER
                        {whereClause}
                    ) WHERE rn > :offset AND rn <= :offset + :pageSize";

                var data = await connection.QueryAsync<RefundTaskMaster>(dataSql, parameters);

                return (data, totalCount);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error getting refund task masters with filter");
                throw; //test
            }
        }

        public async Task<IEnumerable<RefundTaskMaster>> GetByUsernameAsync(string username)
        {
            try
            {
                using var connection = _connectionFactory.CreateConnection();
                const string sql = @"
                    SELECT ID as Id, 
                           INPUT_DATE as InputDate, 
                           USERNAME as Username, 
                           FULLNAME as Fullname, 
                           ACCOUNT_COUNT as AccountCount, 
                           ACCOUNT_SUM_BALANCE as AccountSumBalance
                    FROM PCA.LZFY_REFUND_TASK_MASTER
                    WHERE UPPER(USERNAME) = UPPER(:username)
                    ORDER BY INPUT_DATE DESC";

                return await connection.QueryAsync<RefundTaskMaster>(sql, new { username });
            }
            catch (OracleException ex) when (ex.Number == 50012 || ex.Message.Contains("Pooled connection request timed out"))
            {
                _logger.LogError(ex, "Oracle连接池耗尽，获取用户退费任务失败: {Username}", username);
                throw new InvalidOperationException("数据库连接繁忙，请稍后重试", ex);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error getting refund task masters by username: {Username}", username);
                throw;
            }
        }

        public async Task<long> CreateAsync(RefundTaskMaster refundTaskMaster, IDbConnection connection, IDbTransaction? transaction = null)
        {
            try
            {
                const string sql = @"
                    INSERT INTO PCA.LZFY_REFUND_TASK_MASTER 
                    (USERNAME, FULLNAME, ACCOUNT_COUNT, ACCOUNT_SUM_BALANCE)
                    VALUES (:username, :fullname, :accountCount, :accountSumBalance)
                    RETURNING ID INTO :id";

                var parameters = new DynamicParameters();
                parameters.Add("username", refundTaskMaster.Username);
                parameters.Add("fullname", refundTaskMaster.Fullname);
                parameters.Add("accountCount", refundTaskMaster.AccountCount);
                parameters.Add("accountSumBalance", refundTaskMaster.AccountSumBalance);
                parameters.Add("id", dbType: DbType.Int64, direction: ParameterDirection.Output);

                await connection.ExecuteAsync(sql, parameters, transaction: transaction);

                return parameters.Get<long>("id");
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error creating refund task master with transaction");
                throw;
            }
        }

        public async Task<bool> UpdateAsync(RefundTaskMaster refundTaskMaster, IDbConnection connection, IDbTransaction? transaction = null)
        {
            try
            {
                const string sql = @"
                    UPDATE PCA.LZFY_REFUND_TASK_MASTER
                    SET USERNAME = :username,
                        FULLNAME = :fullname,
                        ACCOUNT_COUNT = :accountCount,
                        ACCOUNT_SUM_BALANCE = :accountSumBalance
                    WHERE ID = :id";

                var rowsAffected = await connection.ExecuteAsync(sql, new
                {
                    id = refundTaskMaster.Id,
                    username = refundTaskMaster.Username,
                    fullname = refundTaskMaster.Fullname,
                    accountCount = refundTaskMaster.AccountCount,
                    accountSumBalance = refundTaskMaster.AccountSumBalance
                }, transaction: transaction);

                return rowsAffected > 0;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error updating refund task master with transaction: {Id}", refundTaskMaster.Id);
                throw;
            }
        }

        public async Task<bool> UpdateBalanceFromDetailsAsync(long id, IDbConnection connection, IDbTransaction? transaction = null)
        {
            try
            {
                const string sql = @"
                    UPDATE PCA.LZFY_REFUND_TASK_MASTER
                    SET ACCOUNT_SUM_BALANCE = (
                        SELECT COALESCE(SUM(BALANCE), 0)
                        FROM PCA.LZFY_REFUND_TASK_DETAIL
                        WHERE MASTER_ID = :id
                    ),
                    ACCOUNT_COUNT = (
                        SELECT COUNT(*)
                        FROM PCA.LZFY_REFUND_TASK_DETAIL
                        WHERE MASTER_ID = :id
                    )
                    WHERE ID = :id";

                var rowsAffected = await connection.ExecuteAsync(sql, new { id }, transaction: transaction);
                return rowsAffected > 0;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error updating balance from details with transaction for master: {Id}", id);
                throw;
            }
        }

        public async Task<bool> DeleteAsync(long id)
        {
            try
            {
                using var connection = _connectionFactory.CreateConnection();
                const string sql = "DELETE FROM PCA.LZFY_REFUND_TASK_MASTER WHERE ID = :id";

                var rowsAffected = await connection.ExecuteAsync(sql, new { id });
                return rowsAffected > 0;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error deleting refund task master: {Id}", id);
                throw;
            }
        }

        public async Task<RefundTaskMaster?> GetWithDetailsAsync(long id)
        {
            try
            {
                using var connection = _connectionFactory.CreateConnection();
                const string sql = @"
                    SELECT m.ID as Id, 
                           m.INPUT_DATE as InputDate, 
                           m.USERNAME as Username, 
                           m.FULLNAME as Fullname, 
                           m.ACCOUNT_COUNT as AccountCount, 
                           m.ACCOUNT_SUM_BALANCE as AccountSumBalance,
                           d.ID as DetailId, d.INPUT_DATE as DetailInputDate, d.MASTER_ID, d.PATIENT_ID, 
                           d.ACCOUNT_ID, d.ACCOUNT_INDEX, d.BALANCE, d.CONTACT_COUNT,
                           d.CONTACT_TIME1, d.CONTACT_TIME2, d.CONTACT_TIME3, d.CONTACT_TIME4, d.CONTACT_TIME5,
                           d.PHONE_NUMBER, d.PAT_NAME, d.ID_NUMBER, d.SEX, d.NEXT_OF_KIN, d.NEXT_OF_KIN_PHONE,
                           d.GUARDIAN_NAME, d.GUARDIAN_IDCODE
                    FROM PCA.LZFY_REFUND_TASK_MASTER m
                    LEFT JOIN PCA.LZFY_REFUND_TASK_DETAIL d ON m.ID = d.MASTER_ID
                    WHERE m.ID = :id
                    ORDER BY d.INPUT_DATE";

                var masterDict = new Dictionary<long, RefundTaskMaster>();

                await connection.QueryAsync<RefundTaskMaster, RefundTaskDetail, RefundTaskMaster>(
                    sql,
                    (master, detail) =>
                    {
                        if (!masterDict.TryGetValue(master.Id, out var masterEntry))
                        {
                            masterEntry = master;
                            masterDict.Add(master.Id, masterEntry);
                        }

                        if (detail != null)
                        {
                            // Note: This would require adding a Details property to RefundTaskMaster
                            // For now, just return the master without details
                        }

                        return masterEntry;
                    },
                    new { id },
                    splitOn: "DetailId");

                return masterDict.Values.FirstOrDefault();
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error getting refund task master with details: {Id}", id);
                throw;
            }
        }

        public async Task<bool> UpdateBalanceFromDetailsAsync(long id)
        {
            try
            {
                using var connection = _connectionFactory.CreateConnection();
                return await UpdateBalanceFromDetailsAsync(id, connection);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error updating balance from details for master: {Id}", id);
                throw;
            }
        }

        public System.Data.IDbConnection GetConnection()
        {
            return _connectionFactory.CreateConnection();
        }

        public async Task<long> CreateAsync(RefundTaskMaster refundTaskMaster)
        {
            try
            {
                using var connection = _connectionFactory.CreateConnection();
                return await CreateAsync(refundTaskMaster, connection);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error creating refund task master");
                throw;
            }
        }

        public async Task<bool> UpdateAsync(RefundTaskMaster refundTaskMaster)
        {
            try
            {
                using var connection = _connectionFactory.CreateConnection();
                return await UpdateAsync(refundTaskMaster, connection);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error updating refund task master: {Id}", refundTaskMaster.Id);
                throw;
            }
        }

        public async Task<(IEnumerable<RefundTaskMaster> data, int totalCount)> GetAllPagedAsync(int pageIndex = 1, int pageSize = 100)
        {
            try
            {
                using var connection = _connectionFactory.CreateConnection();
                
                // 获取总数
                const string countSql = "SELECT COUNT(*) FROM lzfy_refund_master";
                var totalCount = await connection.QuerySingleAsync<int>(countSql);
                
                if (totalCount == 0)
                {
                    return (Enumerable.Empty<RefundTaskMaster>(), 0);
                }
                
                // 计算分页偏移
                var offset = (pageIndex - 1) * pageSize;
                
                // 获取分页数据
                const string dataSql = @"
                    SELECT ID as Id, 
                           INPUT_DATE as InputDate, 
                           USERNAME as Username, 
                           FULLNAME as Fullname, 
                           ACCOUNT_COUNT as AccountCount, 
                           ACCOUNT_SUM_BALANCE as AccountSumBalance
                    FROM lzfy_refund_master
                    ORDER BY INPUT_DATE DESC
                    OFFSET :offset ROWS FETCH NEXT :pageSize ROWS ONLY";
                
                var parameters = new DynamicParameters();
                parameters.Add("offset", offset);
                parameters.Add("pageSize", pageSize);
                
                var data = await connection.QueryAsync<RefundTaskMaster>(dataSql, parameters);
                
                return (data, totalCount);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error getting paged refund task masters");
                throw;
            }
        }
    }
}